const Database = require('better-sqlite3')
const db = new Database( 'app.db')



const getAll = (userId) => {
    // 使用参数化的查询来防止SQL注入
    const stmt = db.prepare('SELECT * FROM tasks WHERE user_id = ?');
    const rows = stmt.all(userId);
    return rows;
  };
  

const addTask = (text, userIdFromToken) => {
    try {
        const info = db.prepare('insert into tasks(text, user_id) values(?, ?)')
          .run(text, userIdFromToken)
        const stmt = db.prepare('SELECT * FROM tasks WHERE id = ?')
        const rows = stmt.all(info.lastInsertRowid);
        return { status: 0, msg: 'Insert OK!', rows};
    } catch(e) {
        return { status: 1, msg: e.message }
    }
}

const removeById = (taskId, userIdFromToken) => {
    const task = db.prepare('SELECT * FROM tasks WHERE id = ? AND user_id = ?').get(taskId, userIdFromToken);
    if (!task) {
      return { status: 1, msg: 'Task not found or you do not have permission to delete it' };
    }
    const statement = db.prepare('DELETE FROM tasks WHERE id = ? AND user_id = ?');
    const info = statement.run(taskId, userIdFromToken);
    return { status: 0, msg: 'Task deleted successfully' };
  };
  
  const updateById = (taskId, text, userIdFromToken) => {
    const task = db.prepare('SELECT * FROM tasks WHERE id = ? AND user_id = ?').get(taskId, userIdFromToken);
    if (!task) {
      return { status: 1, msg: 'Task not found or you do not have permission to update it' };
    }
    const statement = db.prepare('UPDATE tasks SET text = ? WHERE id = ? AND user_id = ?');
    const info = statement.run(text, taskId, userIdFromToken);
    return { status: 0, msg: 'Task updated successfully' };
  };

  const removeByuserId = (userIdFromToken) => {
    const statement = db.prepare('DELETE FROM tasks where user_id = ?');
    const info = statement.run(userIdFromToken);
    return { status: 0, msg: 'Task deleted successfully' };
  }

module.exports = { getAll, addTask, removeById, updateById, removeByuserId }



